package com.felix.dkwexcel.service;

import com.felix.dkwexcel.domain.ExportDTO;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author felix.ma
 * @create 2018-07-19 14:44
 **/
@Service
public class ExcelService {

    @Value("${excel.basedir}")
    private String basedir;
    @Value("${excel.startline}")
    private int startline;
    @Value("${excel.outfile}")
    private String outfile;

    @Value("${excel.contractno.x}")
    private int contractnoX;
    @Value("${excel.contractno.y}")
    private int contractnoY;

    @Value("${excel.bargainor.x}")
    private int bargainorX;
    @Value("${excel.bargainor.y}")
    private int bargainorY;

    private String errorfile = "";

    private List<String[]> list = new ArrayList<>();

    public void contextLoads() throws IOException {
        File[] files = new File(basedir).listFiles();
        for (File file : files) {
            readExcel(file);
        }
        String[] title = {"来自文件名", "#", "Qty", "Cat#", "Description", "Unit price", "Amount"};
        HSSFWorkbook wc = getHSSFWorkbook("汇总", title, list, null);
        wc.write(new FileOutputStream(outfile + "/汇总.xls"));// 写文件
        createFile("错误的Excel.txt", errorfile);
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    private HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, List<String[]> values, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values.get(i).length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values.get(i)[j]);
            }
        }
        return wb;
    }

    // 读取
    private void readExcel(File is) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
            // 遍历每一行
            boolean flag = false;
            for (int r = startline; r < rowCount; r++) {
                if (flag)
                    break;
                Row row = sheet.getRow(r);
                int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
                // 用来存储每行数据的容器
                String[] model = new String[cellCount + 3];
                // 遍历每一列
                model[0] = is.getName();
                model[1] = sheet.getRow(contractnoY).getCell(contractnoX).getStringCellValue().trim();
                model[2] = sheet.getRow(bargainorY).getCell(bargainorX).getStringCellValue().replace("Bargainor:", "").trim();
                for (int c = 0; c < cellCount; c++) {
                    Cell cell = row.getCell(c); // 获取的数值
                    int cellType = cell.getCellType();
                    String cellValue = null;
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING: // 文本
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                            } else {
                                cellValue = String.valueOf(cell.getNumericCellValue()); // double数字
                                if (cellValue.endsWith("0")) {
                                    cellValue = String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()); // int数字
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空白
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_ERROR: // 错误
                            cellValue = "错误";
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            try {
                                cellValue = cell.getStringCellValue();
                            } catch (IllegalStateException e) {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            }
                            break;
                        default:
                            cellValue = "错误";
                    }
                    if (cellValue.startsWith("Note")) {
                        flag = true;
                        break;
                    }
                    model[c + 3] = cellValue.trim();
                }
                // model放入list容器中
                if (!flag)
                    list.add(model);
            }
        } catch (Exception e) {
            errorfile += is.getName() + "\r\n";
        }
    }

    /**
     * 覆盖存入文件
     *
     * @param name
     * @param data
     */
    private void createFile(String name, String data) {
        try {
            File file = new File(outfile, name);
            FileOutputStream fileOutputStream = null;
            fileOutputStream = new FileOutputStream(file);
            fileOutputStream.write(data.getBytes("UTF-8"));
            fileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private ArrayList<ExportDTO> list2array() {
        ArrayList<ExportDTO> entity = new ArrayList<>();
        for (String[] item : list) {
            try {
                ExportDTO s = newInstance(ExportDTO.class, item, 13);
                entity.add(s);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return entity;
    }

    /* 导出excel的模板*/
    public void export() {
        File[] files = new File(basedir).listFiles();
        for (File file : files) {
            readExcel(file);
        }
        try {
            InputStream is = this.getClass().getResourceAsStream("/TMP.xlsx");
            XLSTransformer former = new XLSTransformer();
            HashMap<String, Object> beanParams = new HashMap<>();
            beanParams.put("entity", list2array());
            Workbook workBook = former.transformXLS(is, beanParams);
            workBook.write(new FileOutputStream(outfile + "/汇总.xlsx"));
            createFile("错误的Excel.txt", errorfile);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SuppressWarnings("unchecked")
    private <X> X newInstance(Class<X> clazz, Object[] args, int paramLength)
            throws Exception {
        for (Constructor cons : clazz.getConstructors()) {
            if (cons.getParameterTypes().length == paramLength) {
                Object[] params = new Object[cons.getParameterTypes().length];
                try {
                    System.arraycopy(args, 0, params, 0, paramLength);
                } catch (Exception e) {
                    errorfile += args[0] + "\r\n";
                }
                return (X) cons.newInstance(params);
            }
        }
        return null;
    }
}
